Returns the IDs of documents that were inserted or replaced by the last statement in the current session.

The same value can also be obtained via the @@session.last_insert_id variable.

mysql> select @@session.last_insert_id;
+--------------------------+
| @@session.last_insert_id |
+--------------------------+
| 11,32                    |
+--------------------------+
1 rows in set

mysql> select LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 25,26,29         |
+------------------+
1 rows in set   

Returns the current connection ID.

mysql> select CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
| 6               |
+-----------------+
1 row in set (0.00 sec)

Returns KNN vector search distance.

mysql> select id, knn_dist() from test where knn ( image_vector, 5, (0.286569,-0.031816,0.066684,0.032926) ) and match('white') and id < 10;
+------+------------+
| id   | knn_dist() |
+------+------------+
|    2 | 0.81527930 |
+------+------------+
1 row in set (0.00 sec)

Backing up your tables on a regular basis is essential for recovery in the event of system crashes, hardware failure, or data corruption/loss. It's also highly recommended to make backups before upgrading to a new Manticore Search version or running ALTER TABLE.

Backing up database systems can be done in two unique ways: logical and physical backups. Each of these methods has its pros and cons, which may vary based on the specific database environment and needs. Here, we'll delve into the distinction between these two types of backups.

Logical backups entail exporting the database schema and data as SQL statements or as data formats specific to the database. This backup form is typically readable by humans and can be employed to restore the database on various systems or database engines.

Pros and cons of logical backups:

• ➕ Portability: Logical backups are generally more portable than physical backups, as they can be used to restore the database on different hardware or operating systems.
• ➕ Flexibility: Logical backups allow you to selectively restore specific tables, indexes, or other database objects.
• ➕ Compatibility: Logical backups can be used to migrate data between different database management systems or versions, provided the target system supports the exported format or SQL statements.
• ➖ Slower Backup and Restore: Logical backups can be slower than physical backups, as they require the database engine to convert the data into SQL statements or another export format.
• ➖ Increased System Load: Creating logical backups can cause higher system load, as the process requires more CPU and memory resources to process and export the data.

Manticore Search supports mysqldump for logical backups.

Physical backups involve copying the raw data files and system files that comprise the database. This type of backup essentially creates a snapshot of the database's physical state at a given point in time.

Pros and cons of physical backups:

• ➕ Speed: Physical backups are usually faster than logical backups, as they involve copying raw data files directly from disk.
• ➕ Consistency: Physical backups ensure a consistent backup of the entire database, as all related files are copied together.
• ➕ Lower System Load: Creating physical backups generally places less load on the system compared to logical backups, as the process does not involve additional data processing.
• ➖ Portability: Physical backups are typically less portable than logical backups, as they may be dependent on the specific hardware, operating system, or database engine configuration.
• ➖ Flexibility: Physical backups do not allow for the selective restoration of specific database objects, as the backup contains the entire database's raw files.
• ➖ Compatibility: Physical backups cannot be used to migrate data between different database management systems or versions, as the raw data files may not be compatible across different platforms or software.

Manticore Search has manticore-backup command line tool for physical backups.

In summary, logical backups provide more flexibility, portability, and compatibility but can be slower and more resource-intensive, while physical backups are faster, more consistent, and less resource-intensive but may be limited in terms of portability and flexibility. The choice between these two backup methods will depend on your specific database environment, hardware, and requirements.

The manticore-backup tool, included in the official Manticore Search packages, automates the process of backing up tables for an instance running in RT mode.

If you followed the official installation instructions, you should already have everything installed and don't need to worry. Otherwise, manticore-backup requires PHP 8.1.10 and specific modules or manticore-executor, which is a part of the manticore-extra package, and you need to ensure that one of these is available.

Note that manticore-backup is not available for Windows yet.

First, make sure you're running manticore-backup on the same server where the Manticore instance you are about to back up is running.

Second, we recommend running the tool under the root user so the tool can transfer ownership of the files you are backing up. Otherwise, a backup will be also made but with no ownership transfer. In either case, you should make sure that manticore-backup has access to the data dir of the Manticore instance.

manticore-backup --config=path/to/manticore.conf --backup-dir=backupdir

Copyright (c) 2023-2024, Manticore Software LTD (https://manticoresearch.com)

Manticore config file: /etc/manticoresearch/manticore.conf
Tables to backup: all tables
Target dir: /mnt/backup/

Manticore config
  endpoint =  127.0.0.1:9308

Manticore versions:
  manticore: 5.0.2
  columnar: 1.15.4
  secondary: 1.15.4
2022-10-04 17:18:39 [Info] Starting the backup...
2022-10-04 17:18:39 [Info] Backing up config files...
2022-10-04 17:18:39 [Info]   config files - OK
2022-10-04 17:18:39 [Info] Backing up tables...
2022-10-04 17:18:39 [Info]   pq (percolate) [425B]...
2022-10-04 17:18:39 [Info]    OK
2022-10-04 17:18:39 [Info]   products (rt) [512B]...
2022-10-04 17:18:39 [Info]    OK
2022-10-04 17:18:39 [Info] Running sync
2022-10-04 17:18:42 [Info]  OK
2022-10-04 17:18:42 [Info] You can find backup here: /mnt/backup/backup-20221004171839
2022-10-04 17:18:42 [Info] Elapsed time: 2.76s
2022-10-04 17:18:42 [Info] Done

manticore-backup --backup-dir=/mnt/backup/ --tables=products

Copyright (c) 2023-2024, Manticore Software LTD (https://manticoresearch.com)

Manticore config file: /etc/manticoresearch/manticore.conf
Tables to backup: products
Target dir: /mnt/backup/

Manticore config
  endpoint =  127.0.0.1:9308

Manticore versions:
  manticore: 5.0.3
  columnar: 1.16.1
  secondary: 0.0.0
2022-10-04 17:25:02 [Info] Starting the backup...
2022-10-04 17:25:02 [Info] Backing up config files...
2022-10-04 17:25:02 [Info]   config files - OK
2022-10-04 17:25:02 [Info] Backing up tables...
2022-10-04 17:25:02 [Info]   products (rt) [512B]...
2022-10-04 17:25:02 [Info]    OK
2022-10-04 17:25:02 [Info] Running sync
2022-10-04 17:25:06 [Info]  OK
2022-10-04 17:25:06 [Info] You can find backup here: /mnt/backup/backup-20221004172502
2022-10-04 17:25:06 [Info] Elapsed time: 4.82s
2022-10-04 17:25:06 [Info] Done

You can also back up your data through SQL by running the simple command BACKUP TO /path/to/backup.

NOTE: BACKUP is not supported in Windows. Consider using mysqldump instead.

NOTE: BACKUP requires Manticore Buddy. If it doesn't work, make sure Buddy is installed.

BACKUP
  [{TABLE | TABLES} a[, b]]
  [{OPTION | OPTIONS}
    async = {on | off | 1 | 0 | true | false | yes | no}
    [, compress = {on | off | 1 | 0 | true | false | yes | no}]
  ]
  TO path_to_backup

For instance, to back up tables a and b to the /backup directory, run the following command:

BACKUP TABLES a, b TO /backup

There are options available to control and adjust the backup process, such as:

• async: makes the backup non-blocking, allowing you to receive a response with the query ID immediately and run other queries while the backup is ongoing. The default value is 0.
• compress: enables file compression using zstd. The default value is 0. For example, to run a backup of all tables in async mode with compression enabled to the /tmp directory:

BACKUP OPTION async = yes, compress = yes TO /tmp

1. The path should not contain special symbols or spaces, as they are not supported.
2. Ensure that Manticore Buddy is launched (it is by default).

To ensure consistency of tables during backup, Manticore Search's backup tools use the innovative FREEZE and UNFREEZE commands. Unlike the traditional lock and unlock tables feature of e.g. MySQL, FREEZE stops flushing data to disk while still permitting writing (to some extent) and selecting updated data from the table.

However, if your RAM chunk size grows beyond the rt_mem_limit threshold during lengthy backup operations involving many inserts, data may be flushed to disk, and write operations will be blocked until flushing is complete. Despite this, the tool maintains a balance between table locking, data consistency, and database write availability while the table is frozen.

When you use manticore-backup or the SQL BACKUP command, the FREEZE command is executed once and freezes all tables you are backing up simultaneously. The backup process subsequently backs up each table one by one, releasing the freeze after successfully backing up each table.

If backup fails or gets interrupted, the tool tries to unfreeze all the tables.

manticore-backup --backup-dir=/mnt/backup/ --restore

Copyright (c) 2023-2024, Manticore Software LTD (https://manticoresearch.com)

Manticore config file:
Backup dir: /tmp/

Available backups: 3
  backup-20221006144635 (Oct 06 2022 14:46:35)
  backup-20221006145233 (Oct 06 2022 14:52:33)
  backup-20221007104044 (Oct 07 2022 10:40:44)

manticore-backup --backup-dir=/mnt/backup/ --restore=backup-20221007104044

Copyright (c) 2023-2024, Manticore Software LTD (https://manticoresearch.com)

Manticore config file:
Backup dir: /tmp/
2022-10-07 11:17:25 [Info] Starting to restore...

Manticore config
  endpoint =  127.0.0.1:9308
2022-10-07 11:17:25 [Info] Restoring config files...
2022-10-07 11:17:25 [Info]   config files - OK
2022-10-07 11:17:25 [Info] Restoring state files...
2022-10-07 11:17:25 [Info]   config files - OK
2022-10-07 11:17:25 [Info] Restoring data files...
2022-10-07 11:17:25 [Info]   config files - OK
2022-10-07 11:17:25 [Info] The backup '/tmp/backup-20221007104044' was successfully restored.
2022-10-07 11:17:25 [Info] Elapsed time: 0.02s
2022-10-07 11:17:25 [Info] Done

mysqldump -h0 -P9306 manticore > manticore_backup.sql
mariadb-dump -h0 -P9306 manticore > manticore_backup.sql

mysqldump -h0 -P9306 --replace --net-buffer-length=16m -etc manticore tbl > tbl.sql

mysql -h0 -P9306 < manticore_backup.sql
mariadb -h0 -P9306 < manticore_backup.sql

Here are some more settings that can be used with mysqldump to tailor your backup:

• -t skips drop/create table commands. Useful for full-text reindexation of a table after changing tokenization settings.
• --no-data: This setting omits table data from the backup, resulting in a backup file that consists only of table schemas.
• --ignore-table=[database_name].[table_name]: This option allows you to bypass a particular table during the backup operation. Note that the database name must be manticore.
• --replace to perform replace instead of insert. Useful for full-text reindexation of a table after changing tokenization settings.
• --net-buffer-length=16M to make batches up to 16 megabytes large for faster restoration.
• -e to batch up documents. Useful for faster restoration.
• -c to keep column names. Useful for reindexation of a table after changing its schema (e.g., changing field order).

For a comprehensive list of settings and their thorough descriptions, kindly refer to the official MySQL documentation.

We recommend specifying the manticore database explicitly when you plan to back up all databases, rather than using the --all-databases option.

Keep in mind that mysqldump does not support backing up distributed tables. Additionally, it cannot back up tables that contain non-stored fields (consider using manticore-backup or the BACKUP SQL command).